CREATE PROCEDURE [dbo].[p_NP_Report_TrendByDealers]
(
	@AccountOrDealerID INT = NULL,
    @AccountType VARCHAR(1) = NULL,
	@ActualAccountType VARCHAR(20) = NULL,
	@TrendBy VARCHAR(1) = NULL,
	@RetailSales INT = NULL,
	@TransactionCount INT = NULL,
	@TerminalCount INT = NULL, 
	@DateStart DATETIME = NULL,
	@DateEnd DATETIME = NULL
)
AS

------------------------------------------------------------------------------------------------------
-- =============================================
----Author  : AJMAL YAZDANI
----Date    : 06/18/2011
----Purpose : Trend By Dealers Report
----History :
----Date          Name                    Comments
-- =============================================
------------------------------------------------------------------------------------------------------

BEGIN
	--------------------GET ALL UNIQUE CARRIER FOR ABOVE ALL ACCOUNTS-------------------------------------------------------------------------------
	CREATE TABLE #TblDealers (DealerID INT, DealerName VARCHAR(50))

	IF(@AccountType = ''A'')
	BEGIN
		INSERT INTO #TblDealers
		SELECT DealerID, DealerName
		FROM Dealers WITH(NOLOCK)
		WHERE Active = 1 AND AccountID = @AccountOrDealerID
	END

	IF(@AccountType = ''D'')
	BEGIN
		INSERT INTO #TblDealers
		SELECT DealerID, DealerName FROM Dealers WITH(NOLOCK) WHERE DealerID = @AccountOrDealerID
	END
	--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

	IF(@TrendBy = ''W'')
	BEGIN
		--------------------WEEK CALCULATION-----------------------------------------------------------------------------------------------------------
		DECLARE @Date DATETIME
		SET @Date = @DateStart
		DECLARE @TblWeekRange TABLE(SrNo INT IDENTITY(1,1), WeekNumber VARCHAR(10))
		WHILE @Date<@DateEnd
		BEGIN
		INSERT INTO @TblWeekRange
		SELECT  CASE WHEN DateName(dw, @Date) = ''SUNDAY'' THEN ''Wk''+ CAST(MONTH( MIN(DATEADD(DAY, ((DATEDIFF(DAY,''19000101'',@Date)/7)*7)+6, ''19000101''))) AS VARCHAR) +  
												''S''+CAST(DAY(MIN(DATEADD(DAY, (DATEDIFF(DAY,''19000101'',@Date)/7*7)+7, ''19000101''))-1) AS VARCHAR) 
				ELSE ''Wk''+CAST(MONTH( MIN(DATEADD(DAY, ((DATEDIFF(DAY,''19000101'',@Date)/7)*7), ''19000101''))) AS VARCHAR) +  
						''S''+CAST(DAY(MIN(DATEADD(DAY, (DATEDIFF(DAY,''19000101'',@Date)/7*7), ''19000101''))-1)AS VARCHAR) END 
		SET @Date=DATEADD(DAY, 7, @Date) END

		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		
		--------------------WEEKLY DATA CALCULATION----------------------------------------------------------------------------------------------------
		CREATE TABLE #TblWeekData (DealerID INT, DealerName VARCHAR(50), WeekNumber VARCHAR(10), TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH DEALERS AS 
				(SELECT C.DealerID, C.DealerName, CTC.POSID
					FROM #TblDealers C 
					CROSS APPLY DBO.fr_NP_SelDealerPOS(C.DealerID) CTC),
		--SALES--
		SALES AS 
				(SELECT C.DealerID, C.DealerName,
						DBO.fr_NP_TrendingMonthWeekFirstDate(PT.ORDERDATE) AS ''WeekNumber'',
						ISNULL(COUNT(DISTINCT C.POSID), 0) AS ''ActivePOSCount'',
						ISNULL(COUNT(PT.ItemID), 0) AS ''TransactionCount'',
						ISNULL(SUM(PT.RetailValue), 0.00) AS ''RetailSales''	
						FROM DEALERS C
						JOIN NP_PinsTransactions PT WITH(NOLOCK) ON C.POSID = PT.PointofSaleID AND PT.OrderDate BETWEEN @DateStart AND @DateEnd		
						GROUP BY C.DealerID, C.DealerName, DBO.fr_NP_TrendingMonthWeekFirstDate(PT.ORDERDATE)),	
		--SALES RETURN--
		SALESRETURNS AS 
				(SELECT C.DealerID, C.DealerName,
						DBO.fr_NP_TrendingMonthWeekFirstDate(PTR.ReturnedDate) AS ''WeekNumber'',
						ISNULL(COUNT(PTR.ItemID), 0) AS ''TransactionReturnsCount'',
						ISNULL(SUM(PTR.RetailValue), 0.00) AS ''RetailSalesReturn''	
						FROM DEALERS C 
						JOIN NP_PinsTransactionsReturns PTR WITH(NOLOCK) ON C.POSID = PTR.PointofSaleID AND PTR.ReturnedDate BETWEEN @DateStart AND @DateEnd	
						GROUP BY C.DealerID, C.DealerName, DBO.fr_NP_TrendingMonthWeekFirstDate(PTR.ReturnedDate))
		--SALES MINUS SALES RETURN
		INSERT INTO #TblWeekData
		SELECT
			COALESCE(S.DealerID, R.DealerID) AS DealerID,
			COALESCE(S.DealerName, R.DealerName) AS DealerName,
			COALESCE(S.WeekNumber, R.WeekNumber) AS WeekNumber,
			COALESCE(S.ActivePOSCount, 0) AS TerminalCount,
			COALESCE(S.TransactionCount, 0) - COALESCE(R.TransactionReturnsCount, 0) AS TransactionCount,
			COALESCE(S.RetailSales, 0.00) - COALESCE(R.RetailSalesReturn, 0.00) AS RetailSales
		FROM SALES S FULL JOIN SALESRETURNS R 
		ON S.WeekNumber = R.WeekNumber AND S.DealerID = R.DealerID
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		
		-------------------FINAL CALCULATION------------------------------------------------------------------------------------------------------------
		CREATE TABLE #ResultWeekData (DealerID INT, DealerName VARCHAR(50), WeekNumber VARCHAR(10), TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH CTE AS
		(
		SELECT TC.DealerID, TC.DealerName, TDR.WeekNumber
		FROM #TblDealers TC
		CROSS APPLY @TblWeekRange TDR
		) 
		INSERT INTO #ResultWeekData
		SELECT  CTE.DealerID, CTE.DealerName, CTE.WeekNumber,
				ISNULL(TMD.TerminalCount, 0) AS TerminalCount,
				ISNULL(TMD.TransactionCount, 0) AS TransactionCount,
				ISNULL(TMD.RetailSales, 0.00) AS RetailSales
		FROM CTE
		LEFT JOIN #TblWeekData TMD ON TMD.DealerName = CTE.DealerName AND TMD.WeekNumber = CTE.WeekNumber
		ORDER BY CTE.DealerName
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

		--------------------PIVOT WEEK CALCULATION-----------------------------------------------------------------------------------------------------
		DECLARE @SqlQueryWEEK NVARCHAR(MAX) 
		SET @SqlQueryWEEK = ''''
		CREATE TABLE #TblWeek (SrNo INT, WeekNumber VARCHAR(10))
		INSERT INTO #TblWeek
		SELECT SrNo, WeekNumber FROM @TblWeekRange
		SET @SqlQueryWEEK = ''SELECT ''''Dealer Name'''' AS DealerName, ''  + STUFF( (SELECT '', '''''' + WeekNumber + ''''''  AS '' + ''COL'' + CAST(SrNo AS VARCHAR) + '''' 
							FROM #TblWeek FOR XML PATH('''')),1,2,'''')
		EXECUTE SP_EXECUTESQL @SqlQueryWEEK
		DROP TABLE #TblWeek
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

		--------------------PIVOT DATA CALCULATION------------------------------------------------------------------------------------------------------
		DECLARE @SQLWEEK NVARCHAR(MAX)
		SET @SQLWEEK = ''''
		
		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + WeekNumber +
									'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + WeekNumber +
									'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + WeekNumber +
									'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLWEEK = @SQLWEEK +	'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + WeekNumber +
									'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + WeekNumber +
									'', MIN (CASE WHEN [WeekNumber] = ''+ QUOTENAME(WeekNumber,'''''''') +
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + WeekNumber
									FROM @TblWeekRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END
		SET @SQLWEEK = ''SELECT [DealerName] '' + @SQLWEEK +  '' FROM #ResultWeekData GROUP BY [DealerID], [DealerName]  ORDER BY [DealerName]''
	 
		EXECUTE SP_EXECUTESQL @SQLWEEK

		DROP TABLE #ResultWeekData
		DROP TABLE #TblWeekData
		------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

	END

	IF(@TrendBy = ''M'')
	BEGIN
		--------------------MONTH CALCULATION-----------------------------------------------------------------------------------------------------------
		DECLARE @TblMonthRange TABLE (SrNo INT, MonthName VARCHAR(20), MonthNumber INT)
		;WITH CTETALLY AS (SELECT TOP (DATEDIFF(mm,
							DATEADD(mm, DATEDIFF(mm, 0, '''' + CAST(@DateStart AS VARCHAR) + ''''),0),  
							DATEADD(mm, DATEDIFF(mm, 0, '''' + CAST(@DateEnd AS VARCHAR) + '''') + 1, 0))) 
							SrNo = ROW_NUMBER() OVER (ORDER BY T1.OBJECT_ID)
							FROM Master.SYS.ALL_COLUMNS T1
							CROSS JOIN Master.SYS.ALL_COLUMNS T2)
		INSERT INTO @TblMonthRange
		SELECT  SrNo, MonthName = SUBSTRING(DATENAME(MONTH,DATEADD(mm, DATEDIFF(mm, 0, '''' + CAST(@DateStart AS VARCHAR) + '''') + T.SrNo - 1,0)), 1, 3),
				MonthNumber = MONTH(DATEADD(mm, DATEDIFF(mm, 0, '''' + CAST(@DateStart AS VARCHAR) + '''') + T.SrNo - 1, 0)) FROM CTETALLY T
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		
		--------------------MONTHLY DATA CALCULATION----------------------------------------------------------------------------------------------------
		CREATE TABLE #TblMonthData (DealerID INT, DealerName VARCHAR(50), MonthNumber INT, TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH DEALERS AS 
				(SELECT C.DealerID, C.DealerName, CTC.POSID
					FROM #TblDealers C 
					CROSS APPLY DBO.fr_NP_SelDealerPOS(C.DealerID) CTC),
		--SALES--
		SALES AS 
				(SELECT C.DealerID, C.DealerName,
						MONTH(PT.ORDERDATE) AS ''MonthNumber'',
						ISNULL(COUNT(DISTINCT C.POSID), 0) AS ''ActivePOSCount'',
						ISNULL(COUNT(PT.ItemID), 0) AS ''TransactionCount'',
						ISNULL(SUM(PT.RetailValue), 0.00) AS ''RetailSales''	
						FROM DEALERS C
						JOIN NP_PinsTransactions PT WITH(NOLOCK) ON C.POSID = PT.PointofSaleID AND PT.OrderDate BETWEEN @DateStart AND @DateEnd		
						GROUP BY C.DealerID, C.DealerName, MONTH(PT.ORDERDATE)),	
		--SALES RETURN--
		SALESRETURNS AS 
				(SELECT C.DealerID, C.DealerName,
						MONTH(PTR.ReturnedDate) AS ''MonthNumber'',
						ISNULL(COUNT(PTR.ItemID), 0) AS ''TransactionReturnsCount'',
						ISNULL(SUM(PTR.RetailValue), 0.00) AS ''RetailSalesReturn''	
						FROM DEALERS C 
						JOIN NP_PinsTransactionsReturns PTR WITH(NOLOCK) ON C.POSID = PTR.PointofSaleID AND PTR.ReturnedDate BETWEEN @DateStart AND @DateEnd	
						GROUP BY C.DealerID, C.DealerName, MONTH(PTR.ReturnedDate))
		--SALES MINUS SALES RETURN
		INSERT INTO #TblMonthData
		SELECT
			COALESCE(S.DealerID, R.DealerID) AS DealerID,
			COALESCE(S.DealerName, R.DealerName) AS DealerName,
			COALESCE(S.MonthNumber, R.MonthNumber) AS MonthNumber,
			COALESCE(S.ActivePOSCount, 0) AS TerminalCount,
			COALESCE(S.TransactionCount, 0) - COALESCE(R.TransactionReturnsCount, 0) AS TransactionCount,
			COALESCE(S.RetailSales, 0.00) - COALESCE(R.RetailSalesReturn, 0.00) AS RetailSales
		FROM SALES S FULL JOIN SALESRETURNS R 
		ON S.MonthNumber = R.MonthNumber AND S.DealerID = R.DealerID
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		CREATE TABLE #ResultMonthData (DealerID INT, DealerName VARCHAR(50), MonthName VARCHAR(5), TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH CTE AS
		(
		SELECT TA.DealerID, TA.DealerName , TDR.MonthName , TDR.MonthNumber
		FROM #TblDealers TA
		CROSS APPLY @TblMonthRange TDR
		) 
		INSERT INTO #ResultMonthData
		SELECT  CTE.DealerID, CTE.DealerName, CTE.MonthName,
				ISNULL(TMD.TerminalCount, 0) AS TerminalCount,
				ISNULL(TMD.TransactionCount, 0) AS TransactionCount,
				ISNULL(TMD.RetailSales, 0.00) AS RetailSales
		FROM CTE
		LEFT JOIN #TblMonthData TMD ON TMD.DealerName = CTE.DealerName AND TMD.MonthNumber = CTE.MonthNumber
		ORDER BY CTE.DealerName

		--------------------PIVOT MONTH CALCULATION----------------------------------------------------------------------------------------------------
		DECLARE @SqlQuery NVARCHAR(MAX) 
		SET @SqlQuery = ''''
		CREATE TABLE #TblMonth (SrNo INT, MonthName VARCHAR(5))
		INSERT INTO #TblMonth
		SELECT SrNo, MonthName FROM @TblMonthRange
		SET @SqlQuery = ''SELECT ''''Dealer Name'''' AS DealerName, ''  + STUFF( (SELECT '', '''''' + MonthName + ''''''  AS '' + ''COL'' + CAST(SrNo AS VARCHAR) + '''' FROM #TblMonth FOR XML PATH('''')),1,2,'''')
		EXECUTE SP_EXECUTESQL @SqlQuery
		DROP TABLE #TblMonth

		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

		--------------------PIVOT DATA CALCULATION----------------------------------------------------------------------------------------------------
		DECLARE @SQL NVARCHAR(MAX)
		SET @SQL = ''''
		
		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + MonthName 
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + MonthName +
									'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + MonthName +
									'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + MonthName +
									'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQL = @SQL +	'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + MonthName +
									'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + MonthName +
									'', MIN (CASE WHEN [MonthName] = ''+ QUOTENAME(MonthName,'''''''') +
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + MonthName
									FROM @TblMonthRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END

		SET @SQL = ''SELECT [DealerName] '' + @SQL +  '' FROM #ResultMonthData GROUP BY [DealerID], [DealerName]  ORDER BY [DealerName]''
	 
		EXECUTE SP_EXECUTESQL @SQL

		DROP TABLE #ResultMonthData
		DROP TABLE #TblMonthData
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
	END

	IF(@TrendBy = ''Y'')
	BEGIN
		--------------------YEARS CALCULATION-----------------------------------------------------------------------------------------------------------
		DECLARE @TblYearRange TABLE (SrNo INT, YearNumber INT)
		;WITH CTETALLY AS (SELECT TOP (DATEDIFF(yyyy,
							DATEADD(yyyy,DATEDIFF(yyyy,0,''''+CAST(@DateStart AS VARCHAR)+''''), 0),  
							DATEADD(yyyy,DATEDIFF(yyyy,0,''''+CAST(@DateEnd AS VARCHAR)+'''') + 1, 0))) 
							SrNo = ROW_NUMBER() OVER (ORDER BY T1.OBJECT_ID)
							FROM Master.SYS.ALL_COLUMNS T1
							CROSS JOIN Master.SYS.ALL_COLUMNS T2)
		INSERT INTO @TblYearRange
		SELECT  SrNo, YearNumber = YEAR(DATEADD(yyyy,DATEDIFF(yyyy, 0, '''' + CAST(@DateStart AS VARCHAR)+'''') + T.SrNo - 1, 0)) FROM CTETALLY T
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		
		--------------------YEARLY DATA CALCULATION----------------------------------------------------------------------------------------------------
		CREATE TABLE #TblYearData (DealerID INT, DealerName VARCHAR(50), YearNumber INT, TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH DEALERS AS 
				(SELECT C.DealerID, C.DealerName, CTC.POSID
					FROM #TblDealers C 
					CROSS APPLY DBO.fr_NP_SelDealerPOS(C.DealerID) CTC),
		--SALES--
		SALES AS 
				(SELECT C.DealerID, C.DealerName,
						YEAR(PT.ORDERDATE) AS ''YearNumber'',
						ISNULL(COUNT(DISTINCT C.POSID), 0) AS ''ActivePOSCount'',
						ISNULL(COUNT(PT.ItemID), 0) AS ''TransactionCount'',
						ISNULL(SUM(PT.RetailValue), 0.00) AS ''RetailSales''	
						FROM DEALERS C
						JOIN NP_PinsTransactions PT WITH(NOLOCK) ON C.POSID = PT.PointofSaleID AND PT.OrderDate BETWEEN @DateStart AND @DateEnd		
						GROUP BY C.DealerID, C.DealerName, YEAR(PT.ORDERDATE)),	
		--SALES RETURN--
		SALESRETURNS AS 
				(SELECT C.DealerID, C.DealerName,
						YEAR(PTR.ReturnedDate) AS ''YearNumber'',
						ISNULL(COUNT(PTR.ItemID), 0) AS ''TransactionReturnsCount'',
						ISNULL(SUM(PTR.RetailValue), 0.00) AS ''RetailSalesReturn''	
						FROM DEALERS C 
						JOIN NP_PinsTransactionsReturns PTR WITH(NOLOCK) ON C.POSID = PTR.PointofSaleID AND PTR.ReturnedDate BETWEEN @DateStart AND @DateEnd	
						GROUP BY C.DealerID, C.DealerName, YEAR(PTR.ReturnedDate))
		--SALES MINUS SALES RETURN
		INSERT INTO #TblYearData
		SELECT
			COALESCE(S.DealerID, R.DealerID) AS DealerID,
			COALESCE(S.DealerName, R.DealerName) AS DealerName,
			COALESCE(S.YearNumber, R.YearNumber) AS YearNumber,
			COALESCE(S.ActivePOSCount, 0) AS TerminalCount,
			COALESCE(S.TransactionCount, 0) - COALESCE(R.TransactionReturnsCount, 0) AS TransactionCount,
			COALESCE(S.RetailSales, 0.00) - COALESCE(R.RetailSalesReturn, 0.00) AS RetailSales
		FROM SALES S FULL JOIN SALESRETURNS R 
		ON S.YearNumber = R.YearNumber AND S.DealerID = R.DealerID

		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
		CREATE TABLE #ResultYearData (DealerID INT, DealerName VARCHAR(50), YearNumber INT, TerminalCount INT, TransactionCount INT, RetailSales MONEY)

		;WITH CTE AS
		(
		SELECT TA.DealerID, TA.DealerName , TDR.YearNumber
		FROM #TblDealers TA
		CROSS APPLY @TblYearRange TDR
		) 
		INSERT INTO #ResultYearData
		SELECT  CTE.DealerID, CTE.DealerName, CTE.YearNumber,
				ISNULL(TMD.TerminalCount, 0) AS TerminalCount,
				ISNULL(TMD.TransactionCount, 0) AS TransactionCount,
				ISNULL(TMD.RetailSales, 0.00) AS RetailSales
		FROM CTE
		LEFT JOIN #TblYearData TMD ON TMD.DealerName = CTE.DealerName AND TMD.YearNumber = CTE.YearNumber
		ORDER BY CTE.DealerName
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

		--------------------PIVOT YEAR CALCULATION-----------------------------------------------------------------------------------------------------
		DECLARE @SqlQueryYEAR NVARCHAR(MAX) 
		SET @SqlQueryYEAR = ''''
		CREATE TABLE #TblYear (SrNo INT, YearNumber VARCHAR(5))
		INSERT INTO #TblYear
		SELECT SrNo, YearNumber FROM @TblYearRange
		SET @SqlQueryYEAR  = ''SELECT ''''Dealer Name'''' AS DealerName, ''  + STUFF( (SELECT '', '''''' + YearNumber + ''''''  AS '' + ''COL'' + CAST(SrNo AS VARCHAR) + '''' 
								FROM #TblYear FOR XML PATH('''')),1,2,'''')
		EXECUTE SP_EXECUTESQL @SqlQueryYEAR
		DROP TABLE #TblYear
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------

		--------------------PIVOT DATA CALCULATION------------------------------------------------------------------------------------------------------
		DECLARE @SQLYEAR NVARCHAR(MAX)
		SET @SQLYEAR = ''''
	 
		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo
			
			SELECT ''TerminalCount'' AS TerminalCount
			END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 0)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + CAST(YearNumber AS VARCHAR) +
									'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 0 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + CAST(YearNumber AS VARCHAR) +
									'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo

			SELECT ''RetailSales'' AS RetailSales, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 0 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + CAST(YearNumber AS VARCHAR) +
									'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo

			SELECT ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END

		IF(@RetailSales = 1 AND @TransactionCount = 1 AND @TerminalCount = 1)
		BEGIN
			SELECT @SQLYEAR = @SQLYEAR +	'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [RetailSales] END) AS  RetailSales_'' + CAST(YearNumber AS VARCHAR) +
									'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') + 
									'' THEN [TransactionCount] END) AS  TransactionCount_'' + CAST(YearNumber AS VARCHAR) +
									'', MIN (CASE WHEN [YearNumber] = ''+ QUOTENAME(YearNumber,'''''''') +
									'' THEN [TerminalCount] END) AS  TerminalCount_'' + CAST(YearNumber AS VARCHAR)
									FROM @TblYearRange ORDER BY SrNo
			
			SELECT ''RetailSales'' AS RetailSales, ''TransactionCount'' AS TransactionCount, ''TerminalCount'' AS TerminalCount
		END
		SET @SQLYEAR = ''SELECT [DealerName] '' + @SQLYEAR +  '' FROM #ResultYearData GROUP BY [DealerID], [DealerName]  ORDER BY [DealerName]''
	 
		EXECUTE SP_EXECUTESQL @SQLYEAR

		DROP TABLE #ResultYearData
		DROP TABLE #TblYearData
		--------------------TILL HERE-------------------------------------------------------------------------------------------------------------------
	END

	DROP TABLE #TblDealers
	
END





IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fr_NP_SelDealerPOS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[fr_NP_SelDealerPOS] (@DealerID INT)

RETURNS @TblPOS TABLE (DealerID INT, POSID INT)

AS BEGIN
--AJMAL YAZDANI
	INSERT INTO @TblPOS
	SELECT DISTINCT @DealerID, PointOfSaleID   
	FROM PointOfSales WITH(NOLOCK) WHERE DealerID = @DealerID
RETURN
END


' 
END